import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """创建数据库连接"""
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        # 打印SQLite库的版本
        print(f"成功连接到SQLite数据库，库版本：{sqlite3.sqlite_version}")
        return conn
    except Error as e:
        print(f"连接数据库失败：{e}")
    return conn

def create_table(conn):
    """创建学生课程表"""
    try:
        sql = """CREATE TABLE IF NOT EXISTS course_records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    student_id TEXT NOT NULL,  -- 学号
                    name TEXT NOT NULL,        -- 姓名
                    course TEXT NOT NULL,      -- 课程名
                    score REAL NOT NULL        -- 成绩
                );"""
        c = conn.cursor()
        c.execute(sql)
        print("课程记录表创建成功（若不存在）")
    except Error as e:
        print(f"创建表失败：{e}")

def insert_records(conn):
    """插入5条学生课程记录"""
    records = [
        ("2023001", "张三", "数学", 92),
        ("2023002", "李四", "英语", 85),
        ("2023003", "王五", "Python", 78),
        ("2023004", "赵六", "数学", 65),
        ("2023005", "孙七", "Python", 90)
    ]
    sql = """INSERT INTO course_records (student_id, name, course, score)
             VALUES (?, ?, ?, ?);"""
    try:
        c = conn.cursor()
        c.executemany(sql, records)
        conn.commit()
        print(f"成功插入{len(records)}条记录")
    except Error as e:
        print(f"插入记录失败：{e}")

def query_records(conn):
    """查询成绩≥80分的记录"""
    sql = "SELECT * FROM course_records WHERE score >= 80;"
    try:
        c = conn.cursor()
        c.execute(sql)
        rows = c.fetchall()
        print("\n查询成绩≥80分的记录：")
        for row in rows:
            print(f"ID:{row[0]}, 学号:{row[1]}, 姓名:{row[2]}, 课程:{row[3]}, 成绩:{row[4]}")
    except Error as e:
        print(f"查询失败：{e}")

def delete_record(conn):
    """删除数学成绩<70分的记录"""
    sql = "DELETE FROM course_records WHERE course = '数学' AND score < 70;"
    try:
        c = conn.cursor()
        c.execute(sql)
        conn.commit()
        print(f"\n删除了{conn.total_changes}条数学成绩<70分的记录")
    except Error as e:
        print(f"删除失败：{e}")

def update_record(conn):
    """修改张三的Python成绩为95分（若存在）"""
    sql = """UPDATE course_records 
             SET score = 95 
             WHERE name = '张三' AND course = 'Python';"""
    try:
        c = conn.cursor()
        c.execute(sql)
        conn.commit()
        print(f"\n修改了{conn.total_changes}条记录（张三的Python成绩）")
    except Error as e:
        print(f"修改失败：{e}")

def main():
    # 数据库文件路径
    database = "D:\\CodeFiles\\Gitee\\Python\\experiment\\students.db"
    
    # 1. 创建连接
    conn = create_connection(database)
    
    if conn is not None:
        # 2. 创建表
        create_table(conn)
        
        # 3. 插入5条记录
        insert_records(conn)
        
        # 4. 查询成绩≥80的记录
        query_records(conn)
        
        # 5. 删除数学成绩<70的记录
        delete_record(conn)
        
        # 6. 修改张三的Python成绩
        update_record(conn)
        
        # 关闭连接
        conn.close()
        print("\n数据库连接已关闭")
    else:
        print("无法创建数据库连接")

if __name__ == "__main__":
    main()